const { exec } = require('../../mysql')

function getNowTime(){
    let nowDate = new Date()
    let year = nowDate.getFullYear()
    let month = nowDate.getMonth()+1
    let day = nowDate.getDate()
    return `${year}-${month}-${day}`
}

// 获取所有的正式员工列表  get 无参
const getOfficialStaffList = () =>{
    let sql = `select * from officialStaff;`
    return exec(sql)
}

// 分页查询 post 参数：每页数据条数pageSize，页数page
const pagingOfficialStaffList = (page,pageSize) => {
    let sql = `select * from officialStaff limit ${(page-1)*pageSize},${pageSize};`
    return exec(sql)
}

// 模糊查询员工名 post 参数为员工名
const searchOfficialStaffName = (staffName) => {
    let sql = `select * from officialStaff where name like '%${staffName}%';`
    return exec(sql)
}

// 查询员工入职时间 post 起始入职时间，截止入职时间
const searchEntryStaffName = (startTime,endTime) => {
    let sql = `select * from officialStaff Where entryTime Between '${startTime}' And '${endTime}';`
    return exec(sql)
}

// 分页查询员工入职时间 post 起始入职时间，截止入职时间，page，pageSize
const pageSearchEntryStaffName = (startTime,endTime,page,pageSize) => {
    let sql = `select * from officialStaff Where entryTime Between '${startTime}' And '${endTime}' limit ${(page-1)*pageSize},${pageSize};`
    return exec(sql)
}

// 删除 get 参数为id
const deleteOfficialStaff = (id) => {
    let sql = `delete from officialstaff where id = ${id};`
    return exec(sql).then(delData => {
        if (delData.affectedRows > 0) {
            return true
        }
        return false
    })
}

// 批量删除 post 参数为批量删除数据id组成的数组
const batchDeleteOfficialStaff = (idAry) => {
    let sql = `delete from officialStaff where id in (${idAry});`
    return exec(sql).then(delData => {
        if (delData.affectedRows > 0) {
            return true
        }
        return false
    })
}

// 增加 post 参数：name,age,gender,phone,address,duty,salary
const addOfficialStaff = (name,age,gender,phone,address,duty,salary) => {
    let entryTime = getNowTime()
    let sql = `insert into officialStaff (name,age,gender,phone,address,entryTime,duty,salary) values(
        '${name}',${age},'${gender}',${phone},'${address}','${entryTime}','${duty}',${salary}
        );
    `
    return exec(sql).then(insertData => {
        if (insertData.affectedRows > 0) {
            return true
        }
        return false
    })
}

// 修改 post 参数：id, name,age,gender,phone,address,duty,salary
const updateOfficialStaff = (id,name,age,gender,phone,address,duty,salary) => {
    let sql = `update officialStaff set name = '${name}',age = ${age},gender = '${gender}',
        phone = ${phone},address = '${address}',duty = '${duty}',salary = ${salary} where id = ${id};
    `
    return exec(sql).then(Data => {
        if (Data.affectedRows > 0) {
            return true
        }
        return false
    })
}

module.exports = {
    getOfficialStaffList,
    pagingOfficialStaffList,
    searchOfficialStaffName,
    deleteOfficialStaff,
    batchDeleteOfficialStaff,
    addOfficialStaff,
    updateOfficialStaff,
    pageSearchEntryStaffName,
    searchEntryStaffName,
}